"""
批量生成学生信息到EXCEL中

学校识别码	学校名称	班级名称	学生姓名	全国学籍号	身份证号
3134000314	合肥市五十五中学	初中2019级1班	柏英睿	G340102200101109850	340102200101109850
3134000314	合肥市五十五中学	初中2019级1班	芮力学	G340102200101219355	340102200101219355
3134000314	合肥市五十五中学	初中2019级1班	西门涵涵	G340102200108103494	340102200108103494
3134000314	合肥市五十五中学	初中2019级1班	繁阳曦	G340102200109184994	340102200109184994

"""
import random
import time

import pymysql

from com.chq.common.CreateIdNumber import gen_id_card, get_names
import openpyxl
from openpyxl import Workbook
from openpyxl.utils import get_column_letter

filename = "D:\\Users\\CHQIU\\Desktop\\全省考生.xlsx"


class class_info:
    """
       班级信息
    """

    def __init__(self, school_id=None, school_name=None, district_id=None, grade_number=None, class_number=None,
                 size=None):
        self.school_id = school_id
        self.school_name = school_name
        self.district_id = district_id
        self.grade_number = grade_number
        self.class_number = class_number
        self.size = size


class student_info:
    """
       学生信息
    """

    def __init__(self, school_id=None, school_name=None, district_id=None, grade_number=None, class_number=None,
                 id_number=None,
                 student_id=None, name=None, sex=None):
        self.school_id = school_id
        self.school_name = school_name
        self.district_id = district_id
        self.grade_number = grade_number
        self.class_number = class_number
        self.id_number = id_number
        self.student_id = student_id
        self.name = name
        self.sex = sex


def get_anhui_peoples(gender, count):
    """
    随机生成安徽省身份证号码、姓名、性别 年龄12至17岁
    :param gender: 性别
    :param count: 条数
    :return: 身份证号码列表
    """
    people_list = get_names("", gender, count)
    for people in people_list:
        area_code = random.choice(
            ["340102", "340103", "340104", "340111", "340121", "340122", "340123", "340124", "340181", "340202",
             "340203",
             "340207", "340208", "340221", "340222", "340223", "340225", "340302", "340303", "340304", "340311",
             "340321",
             "340322", "340323", "340402", "340403", "340404", "340405", "340406", "340421", "340502", "340503",
             "340504",
             "340506", "340521", "340522", "340523", "340602", "340603", "340604", "340621", "340702", "340703",
             "340711",
             "340721", "340802", "340803", "340811", "340822", "340823", "340824", "340825", "340826", "340827",
             "340828",
             "340881", "341002", "341003", "341004", "341021", "341022", "341023", "341024", "341102", "341103",
             "341122",
             "341124", "341125", "341126", "341181", "341182", "341202", "341203", "341204", "341221", "341222",
             "341225",
             "341226", "341282", "341302", "341321", "341322", "341323", "341324", "341502", "341503", "341521",
             "341522",
             "341523", "341524", "341525", "341602", "341621", "341622", "341623", "341702", "341721", "341722",
             "341723",
             "341802", "341821", "341822", "341823", "341824", "341825", "341881"])
        people.id_number = gen_id_card(int(area_code), random.randint(12, 17), gender)
    return people_list


def get_peoples(district_id, gender, count):
    """
       随机生成安徽省身份证号码、姓名、性别 年龄12至17岁
       :param district_id: 区县ID
       :param gender: 性别
       :param count: 条数
       :return: 身份证号码列表
       """
    people_list = get_names("", gender, count)
    for pe in people_list:
        pe.id_number = gen_id_card(int(district_id), random.randint(12, 17), gender)
    return people_list


def insert_students(student_list):
    # 获取数据连接
    connection = pymysql.connect(host="127.0.0.1", user="root", password="root", db="ems", charset="utf8mb4")
    try:
        # 获取会话指针
        with connection.cursor() as cursor:
            # 创建sql语句
            sql = "replace into `examination_student_info`(`examination_id`,`examination_name`," \
                  "`school_id`,`school_name`,`grade_number`,`class_number`,`student_id`,`id_number`,`student_name`) " \
                  "values "
            i = 0
            for s in student_list:
                sql += "(%s,\"%s\",%s,\"%s\",%s,%s,%s,\"%s\",\"%s\")" % (
                    examination_id, examination_name, s.school_id, s.school_name, s.grade_number, s.class_number,
                    s.student_id, s.id_number, s.name)
                if i < len(student_list) - 1:
                    sql += ","
                i += 1
            # 执行sql语句
            cursor.execute(sql)
            # 提交
            connection.commit()
    finally:
        connection.close()


def insert_students_to_excel(student_list):
    try:
        wb = openpyxl.load_workbook(filename)
    except FileNotFoundError:
        # 文件不存在
        # 在内存中创建一个workbook对象，而且会至少创建一个 worksheet
        wb = Workbook()
        # 获取当前活跃的worksheet,默认就是第一个worksheet
        ws = wb.active
        ws['A1'] = "学校识别码"
        ws['B1'] = "学校名称"
        ws['C1'] = "班级名称"
        ws['D1'] = "学生姓名"
        ws['E1'] = "全国学籍号"
        ws['F1'] = "身份证号"

    ws = wb.active
    row = ws.max_row + 1
    for s in student_list:
        ws.cell(row=row, column=1).value = s.school_id
        ws.cell(row=row, column=2).value = s.school_name
        ws.cell(row=row, column=3).value = "初中%s级%s班" % (s.grade_number, s.class_number)
        ws.cell(row=row, column=4).value = s.name
        ws.cell(row=row, column=5).value = "G%s" % s.id_number
        ws.cell(row=row, column=6).value = s.id_number
        row += 1
    # 保存
    print(row)
    wb.save(filename=filename)
    pass


if __name__ == '__main__':
    # 获取数据连接
    connection = pymysql.connect(host="127.0.0.1", user="root", password="root", db="ems", charset="utf8mb4")
    examination_id = 247741104134144
    examination_name = "考试名称"
    city_id = None
    province_id = 34
    student_list = []
    try:
        # 获取会话指针
        with connection.cursor() as cursor:
            # 创建sql语句
            sql = "SELECT school_id,school_name,district_id FROM `school_info` WHERE status_flag=1"
            if province_id != None:
                sql += " and province_id=%s" % province_id
            if city_id != None:
                sql += " and city_id=%s" % city_id
            # 执行sql语句
            cursor.execute(sql)
            # 获取所有记录列表
            results = cursor.fetchall()
    finally:
        connection.close()
    for row in results:
        class_list = []
        school_id = row[0]
        school_name = row[1]
        district_id = row[2]
        # 年级
        grade_number = 2019
        # 班级总数 随机生成3到7个
        # 班级人数随机40至50个
        class_total = random.randint(3, 7)
        class_number = 1
        while class_number <= class_total:
            class_list.append(
                class_info(school_id, school_name, district_id, grade_number, class_number, random.randint(40, 50)))
            class_number += 1
        # 获取身份证信息
        peoples = get_peoples(district_id, random.randint(0, 1), 888)
        for ci in class_list:
            # print((ci.school_id, ci.district_id, ci.grade_number, ci.class_number, ci.size))
            peoples_temp = peoples[:]
            i = 0
            for p in peoples:
                if i < ci.size:
                    si = student_info(ci.school_id, ci.school_name, ci.district_id, ci.grade_number, ci.class_number,
                                      p.id_number, None, p.name)
                    student_list.append(si)
                    print("数组长度：%s" % len(student_list))
                    peoples_temp.remove(p)
                i += 1
            peoples = peoples_temp
    # 批量更新学生信息到EXCEL文件中
    insert_students_to_excel(student_list)
    print("执行完毕")
